YESTERDAYS CLASS DOCUMENT(08/01/2019) Topics Covered: 1.DBMS_PROFILER 2.EXPLAIN PLAN 3.HOW TO MINIMISE COST 4.PERFORMANCE TUNING (CONTINUATION OF 07/01/2019) 1.DBMS_PROFILER: Now consider you are working in production support one procedure is there like this CREATE OR REPLACE PROCEDURE do_something (p_times IN NUMBER) AS l_dummy NUMBER; BEGIN FOR i IN 1 .. p_times LOOP SELECT l_dummy + 1 INTO l_dummy FROM dual; END LOOP; END; / it takes 2 sec to execute yesterday,now 4 hours its taking,so for this what are the steps to be followed AS L1 - help desk - understand AS L2 - analyse AS L3 - fix the bug for example you consider this plsql program begin insert into t1 values(2); - one time executed for i in 1..10 loop - here 1 time this line is executed insert into t1 values(21); - here 21 times this loop is generated end loop; for i in 1..4 loop - here 1 time this line is executed insert into t1 values(30); - here 30 times this loop is generated end loop; end; like this so many loops are there ,for improving performance frist we will analyse in which line problem is there .this work is done by DBMS_PROFILER using the following steps DECLARE l_result BINARY_INTEGER; BEGIN l_result := DBMS_PROFILER.start_profiler(run_comment => 'do_something: ' || SYSDATE); do_something(p_times => 100); l_result := DBMS_PROFILER.stop_profiler; END; / here we have to start the profiler and executing the procedure 100 times and we are stopping the profiler.with this profiler complete we can analyse the time in nanosecond and run id is generated by using this COLUMN runid FORMAT 99999 COLUMN run_comment FORMAT A50 SELECT runid, run_date, run_comment, run_total_time FROM plsql_profiler_runs ORDER BY runid; runid is generated like this RUNID RUN_DATE RUN_COMMENT RUN_TOTAL_TIME ----- --------- ---------------------------------- -------------- 1 21-AUG-03 do_something: 21-AUG-2003 14:51:54 131072000 then we have to use this runid in the following query plsql_profiler_units and plsql_profiler_data where runid = 1 COLUMN runid FORMAT 99999 COLUMN unit_number FORMAT 99999 COLUMN unit_type FORMAT A20 COLUMN unit_owner FORMAT A20 SELECT u.runid, u.unit_number, u.unit_type, u.unit_owner, u.unit_name, d.line#, d.total_occur, d.total_time, d.min_time, d.max_time FROM plsql_profiler_units u JOIN plsql_profiler_data d ON u.runid = d.runid AND u.unit_number = d.unit_number WHERE u.runid = 1 ORDER BY u.unit_number, d.line#; now for the procedure what we have created ,in which line problem is there is identified by the following report RUNID UNIT_NU UNIT_TYPE UNIT_OWNER UNIT_NAME LINE# TOTAL_OCCUR TOTAL_TIME MIN_TIME MAX_TIME ----- ------- --------------- ----------- ------------ ----- ----------- ---------- -------- -------- 1 1 ANONYMOUS BLOCK 4 1 0 0 0 1 1 ANONYMOUS BLOCK 5 1 0 0 0 1 1 ANONYMOUS BLOCK 6 1 0 0 0 1 2 PROCEDURE MY_SCHEMA DO_SOMETHING 4 101 0 0 0 1 2 PROCEDURE MY_SCHEMA DO_SOMETHING 5 100 17408000 0 2048000 from here we can analyse that in 5th line its taking 17408000 time for execution.db in short DBMS_PROFILER : it is a oracle supplied package.using this package we can analyse in which line problem is there we can get the source code of the procedure using select * from user_source where name = 'p1' what problem in procedure is identified by DBMS_PROFILER 2.EXPLAIN PLAN: what problem in query is identified by Explain plan for example consider this query select a.first_name,d.department_name from employees e,departments d where e.department_id = d.department_id; if we execute the query in isqlplus tool,output is shown for 3 rows and explain is shown.by clicking this explain button we can see the queries explain plan comming to cmd prompt there is no explain plan,so let us see how we will see explain plan in cmd prompt sql>set autut on sql>set autot[race]->execution plan is shown here suppose its taking 3 hours for executing a query then only expalin plan can be seen.instead of waiting for the output for 3 hours we have to see the explain plan immediately means then we have to do like this sql>set autot off sql>explain plan for select a.first_name,d.department_name from employees e,departments d where e.department_id = d.department_id; sql>explained sql>select * from table(dbms_xplan.display); we can generate explain plan for the above query Advantage : 3 hours is saved instead of waiting for the output 3.HOW TO MINIMISE COST: for ex consider this query select first_name,salary from emp where salary = (select max(salary) from emp) union select first_name,salary from emp where salary = (select min(salary) from emp) so now work has come and we have created the query means - development data modeller will see the explain plan for this and they will tell that i don't want because cost is 40 in explain plan we can see the cost of the query. how to minimise the cost for the above query can be done in 4 steps 1.query rewrite 2.creating index 3.table partition 4.table fragmentation 1.QUERY REWRITE: we will rewrite the above query as follows select first_name,salary from emp where salary = (select max(salary) from emp) or salary = (select min(salary) from emp); while executing the above query the cost is reduced to 12 now also data modeller is telling to minimise the cost 2.CREATING INDEX: create index idx1 on emp(salary); while executing the above query the cost is reduced to 6 now also data modeller is telling to minimise the cost 3.TABLE PARTITION: i interview question:so already data is in the table,can we partition?yes the output of the query can be done by table partition -> exchange partition now still the query cost has been reduced. 4.TABLE FRAGMENTATION: There is one table name t1 and A is column name in that 5 values are there A 1 2 3 4 5 select a from t1 where a = 5; for executing this query it took 2 sec yesterday,but today it tooks 4 hours.suddenly there is slow in process which means that table volume has been increased. same table yesterday also 5 rows,today also 5 rows,but time taken 4 hours.From this we have to understand that somebody has inserted 50 lakh rows and deleted that 50 lakh rows. while doing delete block will be there but data will be deleted.this is called as table fragmentation. steps to be followed : for example 1.find total size - 5mb 2.used - 1mb 3.unused - 4 mb this unused space will be deleted by using the following command alter table t1 shrink space; by using the above command unused space will be shrinked. 4.PERFORMANCE TUNING CONTINUATION OF 07/01/2019: xml is generated by java developers in real time. interview question? how will you extract xml? we can extract xml using extract function. for example: select first_name,salary from emp; output: steven 24000 lex 17000 neena 28000 alexander 12000 4 rows will be generated. by using xml in select query as dbms_xmlgentab.getxml('select first_name,salary from emp'); only one row will be generated within xml tag<> save the output in d:\new\.xml in notepad by giving this command SELECT extract(value(d), '//first_name/text()').getStringVal() AS first_name, extract(value(d), '//salary/text()').getNumberVal() AS salary rows will be extracted. from this notepad we can see rowset/rowid for generating this through loop we have to use xml.sequence. comming to our applicatio(institutional client) in bulk order form,when clicking trade now button its taking 4 hours for completing the order placed by the customer.we have written the following code for improving performance of the query BULK ORDERS sno exchange script Market Price Qty Limit Price Stoploss 1. Nse IDEA 116.45 10 115 100 2. bse TCS 1000 20 990 965 3. NSE DLF 120 100 119 110 4. BSE SAIL 104 200 100 90 5. 1000. NSE HCL 120 100 119 110 Trade Now CREATE TABLE T_XML_ORDERS(ORDERS XMLTYPE); CREATE OR REPLACE PROCEDURE SP_BULK_ORDERS ( IN_ORDERS IN CLOB, OUT_ORDER_DETAILS OUT SYS_REFCURSOR ) AS BEGIN INSERT INTO T_XML_ORDERS VALUES (XMLTYPE(IN_ORDERS)); COMMIT; OPEN OUT_ORDER_DETAILS FOR SELECT extract(value(d), '//EXCHANGE/text()').getStringVal() AS EXCHANGE, extract(value(d), '//ORDER_ID/text()').getNumberVal() AS ORDER_ID FROM T_XML_ORDERS x, table(xmlsequence(extract(x.ORDERS, '/ROWSET/ROW'))) d; END; / Explanation: 1.we are creating one table named t_xml_orders in which there is one column name orders and its datatype is XMLTYPE 2.we are creating one stored procedure name SP_BULK_ORDERS in which we are getting one input IN_ORDERS in CLOB datatype-input given by java developer - clob one output OUT_ORDER-DETAILS in SYS_REFCURSOR -output given by java developer 3.all 1000 datas are inserted into the table t_xml_orders in which XMLTYPE is a function which receives the input 1000 datas from IN_ORDERS to convert string to xml we are using xmltype(in_orders) 4.it is saved 5.next OUT_ORDER_DETAILS is opened and all 1000 records are extracted into a single row and loop is generated by using xmlsequence. datatypes: xmltype stores xml clob stores string - 4GB SYS_REFCURSOR stores query result conversion: to convert string to xml use xmlfunction XMLTYPE interview question: what is the last table used : t_xml_orders what is the last procedure used : SP_BULK_ORDERS what is the column name last used : orders what is the last function used : XMLTYPE How java developer will call the trade now button-by using API input - xml output - ref cursor in your project where you have used performance tuning? in bulk order form while clicking trade now button it takes 4 hours for placing 1000 orders,now by using xmltype all 1000 records are inserted within 1 sec.this is the last performace tuning we have done and the performance of the query has been increase and permanent solution is given by l2 and l3 has fixed.